package com.turbo.cloud.ods.utils;

import cn.hutool.core.util.StrUtil;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectItem;
import org.springframework.util.CollectionUtils;

import java.util.*;

/**
 * SQL解析
 *
 * @author zhangluning
 */
@Slf4j
public class SqlUtil {

    /**
     * 查询字段
     */
    private static final String COLUMN_ASTERISK = "*";

    /**
     * 检查SQL
     *
     * @param sqlStr SQL串
     */
    public static boolean checkSql(String sqlStr) {
        try {
            CCJSqlParserUtil.parse(sqlStr);
            return true;
        } catch (JSQLParserException e) {
            log.error("检查SQL解析异常", e);
            return false;
        }
    }

    /**
     * 验证SQL是Select语句
     *
     * @param sqlStr SQL串
     * @return 是否查询SQL
     */
    public static boolean verifySelectSql(String sqlStr) {
        try {
            Statement statement = CCJSqlParserUtil.parse(sqlStr);
            return statement instanceof Select;
        } catch (Exception e) {
            log.error("验证SQL解析异常", e);
            return false;
        }
    }

    /**
     * 验证SQL是Select语句，并且查询字段不能是（*）
     *
     * @param sqlStr SQL串
     */
    public static boolean verifySelectSqlAndNoAsteriskColumn(String sqlStr) {
        try {
            Statement statement = CCJSqlParserUtil.parse(sqlStr);
            if (statement instanceof Select selectStatement) {
                PlainSelect plainSelect = selectStatement.getPlainSelect();
                List<SelectItem<?>> list = plainSelect.getSelectItems();
                if (!CollectionUtils.isEmpty(list)) {
                    String column = list.get(0).getExpression().toString();
                    return !COLUMN_ASTERISK.equals(column);
                }
            }
        } catch (Exception e) {
            log.error("验证SQL解析异常", e);
        }
        return false;
    }


    /**
     * 验证SQL是Select语句，并且查询字段需要定义别名
     *
     * @param sqlStr SQL串
     */
    public static String verifySelectSqlAndHasAliasColumn(String sqlStr) {
        try {
            Statement statement = CCJSqlParserUtil.parse(sqlStr);
            if (statement instanceof Select selectStatement) {
                PlainSelect plainSelect = selectStatement.getPlainSelect();
                List<SelectItem<?>> list = plainSelect.getSelectItems();
                if (!CollectionUtils.isEmpty(list)) {
                    for (SelectItem<?> item : list) {
                        if (Objects.isNull(item.getAlias()) || StrUtil.isBlank(item.getAlias().getName())) {
                            // 提示出具体哪个字段没有别名
                            return "字段(" + item.getExpression().toString() + ")别名不能为空";
                        }
                    }
                }
            }
        } catch (Exception e) {
            log.error("验证SQL解析异常", e);
        }
        return null;
    }

    /**
     * 解析SQL获取Select语句的查询字段
     *
     * @param sqlStr SQL串
     */
    public static List<String> getSelectColumn(String sqlStr) {
        try {
            Statement statement = CCJSqlParserUtil.parse(sqlStr);

            if (statement instanceof Select selectStatement) {
                PlainSelect plainSelect = selectStatement.getPlainSelect();
                List<SelectItem<?>> list = plainSelect.getSelectItems();
                if (!CollectionUtils.isEmpty(list)) {
                    List<String> columns = new ArrayList<>();
                    for (SelectItem<?> item : list) {
                        if (Objects.nonNull(item.getAlias()) && StrUtil.isNotBlank(item.getAlias().getName())) {
                            // 获取别名，去一下空格
                            columns.add(StrUtil.trim(item.getAlias().getName()));
                        } else {
                            columns.add(item.getExpression().toString());
                        }
                    }
                    return columns;
                }
            }
        } catch (Exception e) {
            log.error("验证SQL解析异常", e);
        }
        return null;
    }

    /**
     * 组装SQL参数
     *
     * @param sqlStr   SQL串
     * @param paramMap SQL参数
     */
    public static String joinSqlWhere(String sqlStr, HashMap<String, String> paramMap) {
        if (StrUtil.isBlank(sqlStr) || Objects.isNull(paramMap)) {
            return sqlStr;
        }

        String where = "";
        for (Map.Entry<String, String> entry : paramMap.entrySet()) {
            String key = entry.getKey();
            String value = entry.getValue();
            //防止SQL注入
            value = value.replaceAll("'","");
            // #{key}
            // 例如 where&u.username&=
            // 例如 where&u.username&like&*%
            // 例如 where&u.username&like&%*
            // 例如 where&u.username&like&%%
            // 例如 where&u.username&in
            if (StrUtil.isBlank(where) && StrUtil.isNotBlank(key)) {
                if (key.contains("where&") && StrUtil.isNotBlank(value)) {
                    String columnWhere = key.replace("where&", "").replace("&", " ");
                    if (columnWhere.contains(" =")) {
                        where = " WHERE " + columnWhere + "'" + value + "'";
                    } else if (columnWhere.contains(" = ")) {
                        where = " WHERE " + columnWhere + " '" + value + "'";
                    } else if (columnWhere.contains(" like ") && columnWhere.contains("*%")) {
                        columnWhere = columnWhere.replace("*%", "");
                        where = " WHERE " + columnWhere + "'" + value + "%'";
                    } else if (columnWhere.contains(" like ") && columnWhere.contains("%*")) {
                        columnWhere = columnWhere.replace("%*", "");
                        where = " WHERE " + columnWhere + "'%" + value + "'";
                    } else if (columnWhere.contains(" like ") && columnWhere.contains("%%")) {
                        columnWhere = columnWhere.replace("%%", "");
                        where = " WHERE " + columnWhere + "'%" + value + "%'";
                    } else if (columnWhere.contains(" in") || columnWhere.contains(" in ")) {
                        String[] values = value.split(",");
                        String inWhere = "";
                        for (String str : values) {
                            if (StrUtil.isBlank(inWhere)) {
                                inWhere = "'" + str + "'";
                            } else {
                                inWhere += ",'" + str + "'";
                            }
                        }
                        where = " WHERE " + columnWhere + " (" + inWhere + ")";
                    }
                }
            } else {
                if (key.contains("where&") && StrUtil.isNotBlank(value)) {
                    String columnWhere = key.replace("where&", "").replace("&", " ");
                    if (columnWhere.contains(" =")) {
                        where += " AND " + columnWhere + " '" + value + "'";
                    } else if (columnWhere.contains(" = ")) {
                        where += " AND " + columnWhere + "'" + value + "'";
                    } else if (columnWhere.contains(" like ") && columnWhere.contains("*%")) {
                        columnWhere = columnWhere.replace("*%", "");
                        where += " AND " + columnWhere + "'" + value + "%'";
                    } else if (columnWhere.contains(" like ") && columnWhere.contains("%*")) {
                        columnWhere = columnWhere.replace("%*", "");
                        where += " AND " + columnWhere + "'%" + value + "'";
                    } else if (columnWhere.contains(" like ") && columnWhere.contains("%%")) {
                        columnWhere = columnWhere.replace("%%", "");
                        where += " AND " + columnWhere + "'%" + value + "%'";
                    } else if (columnWhere.contains(" in") || columnWhere.contains(" in ")) {
                        String[] values = value.split(",");
                        String inWhere = "";
                        for (String str : values) {
                            if (StrUtil.isBlank(inWhere)) {
                                inWhere = "'" + str + "'";
                            } else {
                                inWhere += ",'" + str + "'";
                            }
                        }
                        where += " AND " + columnWhere + " (" + inWhere + ")";
                    }
                }
            }
        }
        return sqlStr + where;
    }
}
